﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Web.Configuration;
public partial class Personal_Statistic : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Request["id"]))
        {
            Response.Write("Lacks paramerter");
            return;
        }
        int pant_id = Convert.ToInt32(Request["id"].ToString());
        string sql_script =
@"
SELECT q.part_id AS part_id, (SELECT p.title FROM [Parts] AS p WHERE p.id = q.part_id) AS part_title ,SUM(a.mark) AS total_mark, AVG(a.mark) AS avg_mark
FROM
[Answers] AS a 
JOIN [Questions] AS q
ON a.question_id = q.id
WHERE a.participant_id = @pant_id
GROUP BY q.part_id
";
        IDbConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["SurveyConnectionString"].ConnectionString);
        using (conn)
        {
            conn.Open();
            IDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql_script;
            IDbDataParameter param = cmd.CreateParameter();
            param.ParameterName = "@pant_id";
            param.DbType = DbType.Int32;
            param.Value = pant_id;
            cmd.Parameters.Add(param);
            IDataReader dr = cmd.ExecuteReader();

            GridView1.DataSource = dr;
            GridView1.DataBind();
            dr.Close();
        }
  
    }
}
